<?php
/**
 * GioCMS
 * 
 * LICENSE
 *
 * This source file is subject to the GNU GENERAL PUBLIC LICENSE Version 2 
 * that is bundled with this package in the file LICENSE.txt.
 * It is also available through the world-wide-web at this URL:
 * http://www.gnu.org/licenses/gpl-2.0.txt
 * If you did not receive a copy of the license and are unable to
 * obtain it through the world-wide-web, please send an email
 * to license@ninhgio.com so we can send you a copy immediately.
 * 
 * @copyright	Copyright (c) 2010-2011 GioCMS (http://cms.ninhgio.com)
 * @license		http://www.gnu.org/licenses/gpl-2.0.txt GNU GENERAL PUBLIC LICENSE Version 2
 * @author		NinhGio - ninhgio@gmail.com
 * @since		1.0
 */

class Modules_Category_Models_Mysql_Category extends Modules_Category_Models_Category
{
	protected $_table = 'category';
	
	public function getById($id) 
	{
		$sql = sprintf("SELECT * FROM " . $this->_conn->_tablePrefix . $this->_table . "
						WHERE category_id = '%s'
						LIMIT 1", 
						$this->_conn->escape($id));
		$rs = $this->_conn->query($sql);
		$return = (0 == $this->_conn->count($rs)) ? null : $this->_conn->fetchAll($rs);
		$this->_conn->freeResult($rs);
		return $return;
	}
	
	public function getByPath($categoryPath) 
	{
		$sql = sprintf("SELECT * FROM " . $this->_conn->_tablePrefix . $this->_table . "
						WHERE category_path = '%s'
						LIMIT 1", 
						$this->_conn->escape($categoryPath));
		$rs = $this->_conn->query($sql);
		$return = (0 == $this->_conn->count($rs)) ? null : $this->_conn->fetchAll($rs);
		$this->_conn->freeResult($rs);
		return $return;
	}
	
	public function getSubCategories($categoryId) 
	{
		$sql  = sprintf("SELECT * FROM " . $this->_conn->_tablePrefix . $this->_table . " 
						WHERE parent_id = '%s' AND language = '%s'",
						$this->_conn->escape($categoryId),
						$this->_conn->escape($this->getLang()));
						
		$rs = $this->_conn->query($sql);
		$rows = array();
		while ($row = $this->_conn->fetchAll($rs)) {
			$rows[] = $row;
		}
		/**
		 * Free result 
		 */
		$this->_conn->freeResult($rs);
		return $rows;
	}
	
	public function add($category) 
	{
		$parentId = $category['parent_id'];
		if ($parentId) {
			$sql = sprintf("SELECT right_id FROM " . $this->_conn->_tablePrefix . $this->_table . " 
							WHERE category_id = '%s' AND module_id = '%s' AND language = '%s'
							LIMIT 1",
							$this->_conn->escape($parentId),
							$this->_conn->escape($category['module_id']),
							$this->_conn->escape($category['language']));
		} else {
			$sql = sprintf("SELECT MAX(right_id) AS right_id FROM " . $this->_conn->_tablePrefix . $this->_table . "  
							WHERE module_id = '%s' AND language = '%s'
							LIMIT 1",
							$this->_conn->escape($category['module_id']),
							$this->_conn->escape($category['language']));
		}
		$rs = $this->_conn->query($sql);
		$row = (0 == $this->_conn->count($rs)) ? null : $this->_conn->fetchAll($rs);
		$this->_conn->freeResult($rs);
		$rightId = ($parentId) ? $row['right_id'] : $row['right_id'] + 1;
		
		if ($rightId != null) {
			$sql = sprintf("UPDATE " . $this->_conn->_tablePrefix . $this->_table . "
							SET left_id = IF(left_id > %s, left_id + 2, left_id), 
								right_id = IF(right_id >= %s, right_id + 2, right_id)
							WHERE module_id = '%s' AND language = '%s'", 
							$this->_conn->escape($rightId),
							$this->_conn->escape($rightId),
							$this->_conn->escape($category['module_id']),
							$this->_conn->escape($category['language']));
			$this->_conn->query($sql);							
			
			/**
			 * Keep the category Id in case user update its parent
			 */
			if (isset($category['category_id']) && $category['category_id'] != null) {
				$sql = sprintf("INSERT INTO " . $this->_conn->_tablePrefix . $this->_table . " (category_id, name, slug, category_path, meta, created_date, modified_date, user_id, left_id, right_id, parent_id, language, module_id, status)
								VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')",
								$this->_conn->escape($category['category_id']),
								$this->_conn->escape($category['name']),
								$this->_conn->escape($category['slug']),
								$this->_conn->escape($category['category_path']),
								$this->_conn->escape($category['meta']),
								$this->_conn->escape($category['created_date']),
								$this->_conn->escape($category['modified_date']),
								$this->_conn->escape($category['user_id']),
								$this->_conn->escape($rightId),
								$this->_conn->escape($rightId + 1),
								$this->_conn->escape($parentId),
								$this->_conn->escape($category['language']),
								$this->_conn->escape($category['module_id']),
								$this->_conn->escape($category['status']));
				$this->_conn->query($sql);
				return $category['category_id'];				
			} else {
				$sql = sprintf("INSERT INTO " . $this->_conn->_tablePrefix . $this->_table . " (name, slug, category_path, meta, created_date, modified_date, user_id, left_id, right_id, parent_id, language, module_id, status)
								VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')",
								$this->_conn->escape($category['name']),
								$this->_conn->escape($category['slug']),
								$this->_conn->escape($category['category_path']),
								$this->_conn->escape($category['meta']),
								$this->_conn->escape($category['created_date']),
								$this->_conn->escape($category['modified_date']),
								$this->_conn->escape($category['user_id']),
								$this->_conn->escape($rightId),
								$this->_conn->escape($rightId + 1),
								$this->_conn->escape($parentId),
								$this->_conn->escape($category['language']),
								$this->_conn->escape($category['module_id']),
								$this->_conn->escape($category['status']));
				$this->_conn->query($sql);
				return $this->_conn->lastInsertId();
			}
		}
	}
	
	public function update($category) 
	{
		$sql = sprintf("UPDATE " . $this->_conn->_tablePrefix . $this->_table . "
						SET name = '%s', slug = '%s', category_path = '%s', meta = '%s', modified_date = '%s', language = '%s', status = '%s'
						WHERE category_id = '%s'",
						$this->_conn->escape($category['name']),
						$this->_conn->escape($category['slug']),
						$this->_conn->escape($category['category_path']),
						$this->_conn->escape($category['meta']),
						$this->_conn->escape($category['modified_date']),
						$this->_conn->escape($category['language']),
						$this->_conn->escape($category['status']),
						$this->_conn->escape($category['category_id']));
		$this->_conn->query($sql);
	}
	
	public function delete($category) 
	{
		$sql = sprintf("DELETE FROM " . $this->_conn->_tablePrefix . $this->_table . "
						WHERE category_id = '%s'", 
						$this->_conn->escape($category['category_id']));
		$this->_conn->query($sql);
		
		$sql = sprintf("UPDATE " . $this->_conn->_tablePrefix . $this->_table . " 
						SET left_id = left_id - 1, right_id = right_id - 1
						WHERE module_id = '%s' AND language = '%s'
							AND left_id BETWEEN %s AND %s",
						$this->_conn->escape($category['module_id']),
						$this->_conn->escape($category['language']),
						$this->_conn->escape($category['left_id']), 
						$this->_conn->escape($category['right_id']));
		$this->_conn->query($sql);

		$sql = sprintf("UPDATE " . $this->_conn->_tablePrefix . $this->_table . "
						SET right_id = right_id - 2
						WHERE module_id = '%s' AND language = '%s' AND right_id > %s",
						$this->_conn->escape($category['module_id']),
						$this->_conn->escape($category['language']),
						$this->_conn->escape($category['right_id']));
		$this->_conn->query($sql);
		
		$sql = sprintf("UPDATE " . $this->_conn->_tablePrefix . $this->_table . " 
						SET left_id = left_id - 2
						WHERE module_id = '%s' AND language = '%s' AND left_id > %s",
						$this->_conn->escape($category['module_id']),
						$this->_conn->escape($category['language']),
						$this->_conn->escape($category['right_id']));
		$this->_conn->query($sql);
	}
	
	public function getTree($module) 
	{
		$sql  = sprintf("SELECT node.*, (COUNT(parent.name) - 1) AS depth
						FROM " . $this->_conn->_tablePrefix . $this->_table . " AS node,
							" . $this->_conn->_tablePrefix . $this->_table . " AS parent
						WHERE node.left_id BETWEEN parent.left_id AND parent.right_id
							AND node.language = '%s' 
							AND parent.language = '%s'
							AND node.module_id = '%s'
							AND parent.module_id =  '%s'
						GROUP BY node.category_id
						ORDER BY node.left_id",
						$this->_conn->escape($this->_lang),
						$this->_conn->escape($this->_lang),
						$this->_conn->escape($module),
						$this->_conn->escape($module));
		$rs = $this->_conn->query($sql);
		$rows = array();
		while ($row = $this->_conn->fetchAll($rs)) {
			$rows[] = $row;
		}
		/**
		 * Free result 
		 */
		$this->_conn->freeResult($rs);
		return $rows;
	}
	
	public function getParents($categoryId)
	{
		$sql  = sprintf("SELECT parent.* 
						FROM " . $this->_conn->_tablePrefix . $this->_table . " AS node, " . $this->_conn->_tablePrefix . $this->_table . " AS parent
						WHERE node.left_id BETWEEN parent.left_id AND parent.right_id
							AND node.category_id = %s
						ORDER BY parent.left_id",
						$this->_conn->escape($categoryId));
						
		$rs = $this->_conn->query($sql);
		$rows = array();
		while ($row = $this->_conn->fetchAll($rs)) {
			$rows[] = $row;
		}
		/**
		 * Free result 
		 */
		$this->_conn->freeResult($rs);
		return $rows;
	}
	
	public function updateParents($lang, $module)
	{
		$sql = sprintf("SELECT node.category_id
						FROM " . $this->_conn->_tablePrefix . $this->_table . " AS node,
							" . $this->_conn->_tablePrefix . $this->_table . " AS parent
						WHERE node.left_id BETWEEN parent.left_id AND parent.right_id
							AND node.language = '%s'
							AND parent.language = '%s'
							AND node.module_id = '%s'
						GROUP BY node.category_id
						HAVING (COUNT(parent.name) - 1) = 0",
						$this->_conn->escape($lang),
						$this->_conn->escape($lang),
						$this->_conn->escape($module));
		$rs = $this->_conn->query($sql);
		$rows = array();
		while($row = $this->_conn->fetchAll($rs)) {
			$rows[] = $row['category_id'];
		}
		$this->_conn->freeResult($rs);
		$stringId = implode(',', $rows);
		$sql = sprintf("UPDATE " . $this->_conn->_tablePrefix . $this->_table . " SET parent_id = 0 WHERE category_id IN (%s)", $stringId);
		$this->_conn->query($sql);
		return $this->_conn->affectedRows();
	}
	
	public function count($condition = array())
	{
		$sql = 'SELECT COUNT(*) as num_categories FROM ' . $this->_conn->_tablePrefix . $this->_table;
		$where = ' WHERE 1';
		if (isset($condition['parent_id'])) {
			$where .= ' AND parent_id = "' . $this->_conn->escape($condition['parent_id']) . '"';
		}
		$sql .= $where;
		$rs = $this->_conn->query($sql);
		$row = $this->_conn->fetchAll($rs);
		/**
		 * Free result 
		 */
		$this->_conn->freeResult($rs);
		return $row['num_categories'];
	}
	
	public function getParentId($categoryId)
	{
		$sql = sprintf("SELECT c2.category_id
						FROM " . $this->_conn->_tablePrefix . $this->_table . " AS c1
						INNER JOIN " . $this->_conn->_tablePrefix . $this->_table . " AS c2
							ON c1.left_id BETWEEN c2.left_id AND c2.right_id
						WHERE c1.category_id = %s AND c2.category_id <> %s",
						$this->_conn->escape($categoryId),
						$this->_conn->escape($categoryId));
						
		$rs = $this->_conn->query($sql);
		if (0 == $this->_conn->count($rs)) {
			return $categoryId;
		}
		$row = $this->_conn->fetchAll($rs);
		$return = $row['category_id'];
		/**
		 * Free result 
		 */
		$this->_conn->freeResult($rs);
		return $return;		
	}
	
	public function updateOrder($category)
	{
		$sql = sprintf("UPDATE " . $this->_conn->_tablePrefix . $this->_table . "
						SET parent_id = %s, left_id = %s, right_id = %s
						WHERE category_id = %s",
						$this->_conn->escape($category['parent_id']),
						$this->_conn->escape($category['left_id']),
						$this->_conn->escape($category['right_id']),
						$this->_conn->escape($category['category_id']));
		$this->_conn->query($sql);
	}
	
	/* ========== For translation =========================================== */
	
	public function getTranslations($item, $lang = null)
	{
		$sql  = sprintf("SELECT c.* FROM " . $this->_conn->_tablePrefix . $this->_table . " AS c
						INNER JOIN 
						(
							SELECT tr1.* FROM " . $this->_conn->_tablePrefix . "core_translation AS tr1
							INNER JOIN " . $this->_conn->_tablePrefix . "core_translation AS tr2 
								ON (tr1.item_id = '%s' AND tr1.source_item_id = tr2.item_id) 
								OR (tr2.item_id = '%s' AND tr1.item_id = tr2.source_item_id)
								OR (tr1.source_item_id = '%s' AND tr1.source_item_id = tr2.source_item_id)
							WHERE tr1.item_class = '%s' AND tr2.item_class = '%s'
							GROUP by tr1.translation_id
						) AS tr
							ON tr.item_id = c.category_id", 
						$this->_conn->escape($item['category_id']), 
						$this->_conn->escape($item['category_id']), 
						$this->_conn->escape($item['category_id']),
						'Modules_Category_Models_Category',
						'Modules_Category_Models_Category');
						
		$rs = $this->_conn->query($sql);
		$rows = array();
		while ($row = $this->_conn->fetchAll($rs)) {
			$rows[] = $row;
		}
		/**
		 * Free result 
		 */
		$this->_conn->freeResult($rs);
		return $rows;
	}

	public function getTranslatable($lang, $module)
	{
		$sql = sprintf("SELECT c.*, (tr.item_id IS NULL) AS translatable
						FROM
						(
							SELECT node.category_id, node.name, node.slug, (COUNT(parent.name) - 1) AS depth, 
								node.left_id, node.right_id, node.parent_id
							FROM " . $this->_conn->_tablePrefix . $this->_table . " AS node, 
								" . $this->_conn->_tablePrefix . $this->_table . " AS parent
							WHERE node.left_id BETWEEN parent.left_id AND parent.right_id
								AND node.language = '%s'
								AND parent.language = '%s'
								AND node.module_id = '%s'
							GROUP BY node.category_id
							ORDER BY node.left_id
						) AS c
						LEFT JOIN " . $this->_conn->_tablePrefix . "core_translation AS tr
							ON tr.source_item_id = c.category_id
							AND tr.item_class = '%s'
							AND tr.source_language = '%s'
							AND tr.language = '%s'",
						$this->_conn->escape($this->_lang), 
						$this->_conn->escape($this->_lang),
						$this->_conn->escape($module), 
						'Modules_Category_Models_Category',
						$this->_conn->escape($this->getLang()),
						$this->_conn->escape($lang));
		$rs = $this->_conn->query($sql);
		$rows = array();
		while ($row = $this->_conn->fetchAll($rs)) {
			$rows[] = $row;
		}
		/**
		 * Free result 
		 */
		$this->_conn->freeResult($rs);
		return $rows;
	}
	
	public function getSource($category)
	{
		$sql = sprintf("SELECT c.* 
						FROM " . $this->_conn->_tablePrefix . $this->_table . " AS c
						INNER JOIN " . $this->_conn->_tablePrefix . "core_translation AS tr
							ON c.category_id = tr.source_item_id
						WHERE tr.item_id = '%s' AND tr.item_class = '%s'
						LIMIT 1", 
						$this->_conn->escape($category['category_id']),
						'Modules_Category_Models_Category');
						
		$rs = $this->_conn->query($sql);
		$return = (0 == $this->_conn->count($rs)) ? null : $this->_conn->fetchAll($rs);
		$this->_conn->freeResult($rs);
		return $return;
	}
}